package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.RepresentanteDocDao;
import com.sophossystems.client.asmon.dto.RecolectaDocDTO;
import com.sophossystems.client.asmon.dto.RepresentanteDocDTO;
import com.sophossystems.client.asmon.dto.RepresentanteDocPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.RepresentanteDoc;

public class RepresentanteDocDaoImpl extends AbstractDAO implements
		RepresentanteDocDao {

	
	private static Logger log = Logger.getLogger(RepresentanteDocDaoImpl.class);
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ActivityPk
	 */
	@Override
	@Transactional
	public RepresentanteDocPkDTO insert(final RepresentanteDocDTO dto) {
		RepresentanteDocPkDTO exito = null;
		try {
			exito = (RepresentanteDocPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {
						/**
						 * {@inheritDoc}
						 * 
						 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
						 */
						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							Transaction tx = session.beginTransaction();
							RepresentanteDocPkDTO pk = null;
							RepresentanteDoc representanteDoc = new RepresentanteDoc();
							representanteDoc.mapper(dto);
							session.save(representanteDoc);
							tx.commit();

							try {
								RepresentanteDocPkDTO res = findLastInsert();
								pk = new RepresentanteDocPkDTO();
								pk.setIdRepLegal(res.getIdRepLegal());
							} catch (DaoException es) {

								throw new HibernateException("Insersion: "
										+ es.getMessage());
							}

							return pk;

						}

					});
			// / return exito;
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);

		}
		return exito;
	}

	/**
	 * Updates a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void update(final RepresentanteDocDTO dto) throws DaoException {
		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					RepresentanteDocPkDTO pk = null;
					RepresentanteDocDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = findByPrimaryKey(dto
								.getIdRepLegal());
						pk = new RepresentanteDocPkDTO();
						pk.setIdRepLegal(res.getIdRepLegal());
					} catch (DaoException es) {
						throw new HibernateException("Update RepresentanteDocDao: "
								+ es.getMessage());
					}
					// /aqui se actualizar los campos y con los de la entrada
					// cuidado de actualiza todo.
					RepresentanteDoc persis = new RepresentanteDoc();
					persis.mapper(dto);
					persis.setIdRepLegal(res.getIdRepLegal());
					session.update(persis);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: update representanteDoc, cause: " + e.getMessage(), e);
			throw new DaoException("Error: representanteDoc, cause: "
					+ e.getMessage());
		}

	}

	/**
	 * Deletes a single row in the Activity table.
	 */
	@Override
	@Transactional
	public void delete(final RepresentanteDocPkDTO pk) throws DaoException {

		try {
			getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					Transaction tx = session.beginTransaction();
					RepresentanteDoc representanteDoc = (RepresentanteDoc) session
							.load(RepresentanteDoc.class, pk.getIdRepLegal());
					session.delete(representanteDoc);
					tx.commit();
					return null;
				}
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: "
					+ e.getMessage());
		}
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName() {
		return "RepresentanteDoc";
	}

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'idRepLegal = :idRepLegal'.
	 */
	@Override
	@Transactional
	public RepresentanteDocDTO findByPrimaryKey(final int idRepLegal)
			throws DaoException {
		RepresentanteDocDTO documentos = null;
		try {
			documentos = (RepresentanteDocDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT rp.idRepLegal as idRepLegal, rp.idCliente as idCliente, rp.compDomicilio as compDomicilio, rp.fiel as fiel, rp.compDomicilioFiscal as compDomicilioFiscal, rp.curp as curp, rp.rfc as rfc, rp.otroRfc as otroRfc FROM "
											+ getTableName()
											+ " rp WHERE rp.idRepLegal = :idRepLegal");
							oQuery.setInteger("idRepLegal", idRepLegal);
							RepresentanteDocDTO resultado = (RepresentanteDocDTO) oQuery
									.setResultTransformer(Transformers
													.aliasToBean(RepresentanteDocDTO.class))									
									.list().get(0);
							return resultado;
						}
					});
		} catch (Exception e) {
			logger.error("Error: REPRESENTANTE_DOC, cause: " + e.getMessage(),
					e);
			throw new DaoException("Query failed", e);
		}
		return documentos;

	}

	@Override
	@SuppressWarnings("unchecked")
	/** 
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria ''.
	 */
	@Transactional
	public List<RepresentanteDocDTO> findAll() throws DaoException {
		List<RepresentanteDocDTO> documentosL = null;
		try {
			documentosL = (List<RepresentanteDocDTO>) getHibernateTemplate()
					.execute(new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT doc.idRepLegal as idRepLegal,"
											+ " doc.idCliente as idCliente, doc.compDomicilio as compDomicilio,"
											+ " doc.fiel as fiel, doc.compDomicilioFiscal as compDomicilioFiscal,"
											+ " doc.curp as curp, doc.rfc as rfc, doc.otroRfc as otroRfc FROM "
											+ getTableName()
											+ " doc ORDER BY doc.idRepLegal");
							List<RecolectaDocDTO> lista = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(RepresentanteDocDTO.class))
									.list();
							return lista;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
			// + getTableName() + " ORDER BY idRepLegal", this);
		} catch (Exception e) {
			logger.error("Error: REPRESENTANTE_DOC, cause: " + e.getMessage(),
					e);
			throw new DaoException("Query failed", e);
		}
		return documentosL;
	}

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'idRepLegal = :idRepLegal'.
	 * 
	 * @Transactional public List<RepresentanteDocDTO>
	 *                findWhereIdRepLegalEquals(int idRepLegal) throws
	 *                DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
	 *                + getTableName() +
	 *                " WHERE idRepLegal = ? ORDER BY idRepLegal",
	 *                this,idRepLegal); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_DOC, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	@Override
	@SuppressWarnings("unchecked")
	/** 
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria 'idCliente = :idCliente'.
	 */
	@Transactional
	public List<RepresentanteDocDTO> findWhereIdClienteEquals(
			final int idCliente) throws DaoException {
		List<RepresentanteDocDTO> documentosL = null;
		try {
			documentosL = (List<RepresentanteDocDTO>) getHibernateTemplate()
					.execute(new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT doc.idRepLegal as idRepLegal,"
											+ " doc.idCliente as idCliente, doc.compDomicilio as compDomicilio,"
											+ " doc.fiel as fiel, doc.compDomicilioFiscal as compDomicilioFiscal,"
											+ " doc.curp as curp, doc.rfc as rfc, doc.otroRfc as otroRfc FROM "
											+ getTableName()
											+ " doc WHERE idCliente = :cliente ORDER BY doc.idCliente");
							oQuery.setInteger("cliente", idCliente);
							List<RepresentanteDocDTO> list = oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(RepresentanteDocDTO.class))
									.list();
							return list;
						}
					});
			// return
			// jdbcTemplate.query("SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
			// + getTableName() + " WHERE idCliente = ? ORDER BY idCliente",
			// this,idCliente);
		} catch (Exception e) {
			logger.error("Error: REPRESENTANTE_DOC, cause: " + e.getMessage(),
					e);
			throw new DaoException("Query failed", e);
		}
		return documentosL;
	}

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'compDomicilio = :compDomicilio'.
	 * 
	 * @Transactional public List<RepresentanteDocDTO>
	 *                findWhereCompDomicilioEquals(int compDomicilio) throws
	 *                DaoException { try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
	 *                + getTableName() +
	 *                " WHERE compDomicilio = ? ORDER BY compDomicilio",
	 *                this,compDomicilio); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_DOC, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'fiel = :fiel'.
	 * 
	 * @Transactional public List<RepresentanteDocDTO> findWhereFielEquals(int
	 *                fiel) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
	 *                + getTableName() + " WHERE fiel = ? ORDER BY fiel",
	 *                this,fiel); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_DOC, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'compDomicilioFiscal = :compDomicilioFiscal'.
	 * 
	 * @Transactional public List<RepresentanteDocDTO>
	 *                findWhereCompDomicilioFiscalEquals(int
	 *                compDomicilioFiscal) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
	 *                + getTableName() +
	 *                " WHERE compDomicilioFiscal = ? ORDER BY compDomicilioFiscal"
	 *                , this,compDomicilioFiscal); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_DOC, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'curp = :curp'.
	 * 
	 * @Transactional public List<RepresentanteDocDTO> findWhereCurpEquals(int
	 *                curp) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
	 *                + getTableName() + " WHERE curp = ? ORDER BY curp",
	 *                this,curp); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_DOC, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'rfc = :rfc'.
	 * 
	 * @Transactional public List<RepresentanteDocDTO> findWhereRfcEquals(int
	 *                rfc) throws DaoException { try { return
	 *                jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
	 *                + getTableName() + " WHERE rfc = ? ORDER BY rfc",
	 *                this,rfc); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_DOC, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns all rows from the REPRESENTANTE_DOC table that match the criteria
	 * 'otroRfc = :otroRfc'.
	 * 
	 * @Transactional public List<RepresentanteDocDTO>
	 *                findWhereOtroRfcEquals(int otroRfc) throws DaoException {
	 *                try { return jdbcTemplate.query(
	 *                "SELECT idRepLegal, idCliente, compDomicilio, fiel, compDomicilioFiscal, curp, rfc, otroRfc FROM "
	 *                + getTableName() + " WHERE otroRfc = ? ORDER BY otroRfc",
	 *                this,otroRfc); } catch (Exception e) {
	 *                logger.error("Error: REPRESENTANTE_DOC, cause: " +
	 *                e.getMessage(), e); throw new DaoException("Query failed",
	 *                e); }
	 * 
	 *                }
	 */

	/**
	 * Returns the rows from the REPRESENTANTE_DOC table that matches the
	 * specified primary-key value.
	 * 
	 * public RepresentanteDocDTO findByPrimaryKey(RepresentanteDocPkDTO pk)
	 * throws DaoException { return findByPrimaryKey( pk.getIdRepLegal() ); }
	 */

	@Override
	public RepresentanteDocPkDTO findLastInsert() throws DaoException {
		RepresentanteDocPkDTO representanteDoc = null;
		try {
			representanteDoc = (RepresentanteDocPkDTO) getHibernateTemplate()
					.execute(new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {

							Query oQuery = session
									.createQuery("SELECT max(r.idRepLegal) as idRepLegal FROM "
											+ getTableName() + " r ");

							RepresentanteDocPkDTO representanteDocDto = (RepresentanteDocPkDTO) oQuery
									.setResultTransformer(
											Transformers
													.aliasToBean(RepresentanteDocPkDTO.class))
									.list().get(0);
							return representanteDocDto;
						}
					});

		} catch (Exception e) {
			logger.error(
					"Error: RepresentanteDocDaoImpl, cause: " + e.getMessage(),
					e);
			throw new DaoException("Query failed", e);
		}
		return representanteDoc;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.sophossystems.client.asmon.dao.RepresentanteDocDao#findByPrimaryKey
	 * (com.sophossystems.client.asmon.dto.RepresentanteDocPkDTO)
	 */
	@Override
	public RepresentanteDocDTO findByPrimaryKey(RepresentanteDocPkDTO pk)
			throws DaoException {
		this.findByPrimaryKey(pk.getIdRepLegal());
		return null;
	}
}
